package analyse;

import org.my.stock.util.MyUtil;

import java.beans.PropertyVetoException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class TypeFund {

    /**
     * 获取板块的资金流入
     * */
    public static void update_all_type(String begin, String end) throws SQLException, ClassNotFoundException, PropertyVetoException {
        //List<List<Object>> lstrs = new ArrayList();
        //ResultSet rs = MyUtil.query("select name from stock_type limit 0, 1");
        ResultSet rs = MyUtil.query("select name from stock_type");
        while (rs.next()) {
            // 获取类型的名字
            String name = rs.getString(1);
            // 获取股票编号
            List<Double> symbol = getSymbol(name, begin, end);
            if (!symbol.isEmpty() && symbol.size() > 0) {
                if (isExist(name)) {
                    MyUtil.execute("update type_fund set zhangdie = '"+ symbol.get(0) +"', main_fund = '"+ symbol.get(1) +"', main_fund_bf = '"+ symbol.get(2) +"' where name = '" + name + "'");
                } else {
                    MyUtil.execute("insert into type_fund (name, zhangdie, main_fund, main_fund_bf) values ('" + name + "', " + symbol.get(0) + ", " + symbol.get(1) + ", " + symbol.get(2) + ")");
                }
            }
        }
        rs.close();
    }

    public static boolean isExist(String name) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        PreparedStatement stmt = null;
        stmt = MyUtil.getConnection().prepareStatement("SELECT name FROM type_fund WHERE name = '"+ name +"'");

        // 5. 执行查询并获取结果集
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            flag = true;
        }

        return flag;
    }

    public static List<Double> getFund(String symbol, String begin, String end) throws SQLException, ClassNotFoundException, PropertyVetoException {
        List<Double> lst = new ArrayList<>();
        ResultSet rs = MyUtil.query("SELECT AVG(m.zhangdie), AVG(m.main_fund), AVG(m.main_fund_bf) FROM df_stock_date m WHERE m.SYMBOL = '"+ symbol +"' AND m.STOCK_DATE BETWEEN '"+ begin +"' AND '"+ end +"'");
        while (rs.next()) {
            lst.add(rs.getDouble(1));
            lst.add(rs.getDouble(2));
            lst.add(rs.getDouble(3));
        }
        return lst;
    }

    public static List<Double> getSymbol(String type_name, String begin, String end) throws SQLException, ClassNotFoundException, PropertyVetoException {
        ResultSet rs = MyUtil.query("SELECT m.SYMBOL FROM public.type_stock m where m.type_name = '"+ type_name +"'");
        String name = null;
        Double zhangdie = 0D;
        Double main_fund = 0D;
        Double main_fund_bf = 0D;
        int count = 0;
        List<Double> mylst = new ArrayList<>();
        while (rs.next()) {
            // 获取股票编号
            name = rs.getString(1);
            List<Double> lst = getFund(name, begin, end);
            zhangdie += lst.get(0);
            main_fund += lst.get(1);
            main_fund_bf += lst.get(2);
            count++;
        }
        rs.close();

        if (count > 0) {
            mylst.add(zhangdie / count);
            mylst.add(main_fund / count);
            mylst.add(main_fund_bf / count);
        }
        return mylst;
    }
}


































